<?php

declare(strict_types=1);

namespace App\CustHub\Mapper;

use App\CustHub\Model\ChAddFriendRobot;
use Hyperf\Cache\Annotation\Cacheable;
use Hyperf\DbConnection\Db;
use Mine\Abstracts\AbstractMapper;
use Mine\Exception\NormalStatusException;

class ActiveAddFriendRobotMapper extends AbstractMapper
{
    public function assignModel()
    {
        $this->model = ChAddFriendRobot::class;
    }

    public function robotData($params)
    {
        $uid = (int)$params['uid'];
        $check = $this->model::query()->where('uid', $uid)->exists();

        if (!$check) {
            throw new NormalStatusException("暂无数据");
        }

        $timeRange = $params['time'] ?? null;
        $startDate = null;
        $endDate = null;

        if ($timeRange) {
            $dates = explode(',', $timeRange);
            $startDate = trim($dates[0]) . ' 00:00:00';
            $endDate = isset($dates[1]) ? trim($dates[1]) . ' 23:59:59' : $startDate;
        }

        // 主查询
        return Db::table('ch_add_friend_robot as f')
            ->leftJoin('ch_robot as r', 'f.robot_id', '=', 'r.id')
            ->leftJoin('ch_add_friend_group as rg', 'f.group_id', '=', 'rg.id')
            ->leftJoin('ch_add_friend as fr', function ($join) use ($timeRange, $startDate, $endDate) {
                $join->on('f.id', '=', 'fr.add_robot_id');
                if ($timeRange) {
                    $join->whereBetween('fr.add_time', [$startDate, $endDate]); // **按时间筛选**
                }
            })
            ->where('f.uid', $uid)
            ->select(
                'f.id',
                'rg.group_name',
                'r.nickname',
                'r.avatar',
                'r.corp_name',
                Db::raw('IFNULL(COUNT(fr.id), 0) AS total_greetings'),
                Db::raw('IFNULL(SUM(fr.wechat_status = 2), 0) AS passed_friends'),
                Db::raw('IFNULL(ROUND(IFNULL(SUM(fr.wechat_status = 2), 0) / NULLIF(IFNULL(COUNT(fr.id), 0), 0) * 100, 2), 0) AS pass_rate'),
                Db::raw('IFNULL(SUM(fr.wechat_status = 0), 0) AS not_found_wechat')
            )
            ->groupBy('f.id')
            ->orderBy('f.id');
    }

    #[Cacheable(prefix: 'used_robot_list ',value: 'uid_#{uid}',ttl: 15 * 60)]
    public function usedRobots($uid)
    {
        return $this->model::query()
            ->where('uid', $uid)
            ->where('status', 1)
            ->whereColumn('hello_time', '<', 'max_time')
            ->whereHas('robot', function ($query) {
                $query->where('login_status', 1);
            })
            ->with(['robot' => function ($query) {
                $query->select('id', 'user_id');
            }])
            ->get(['id', 'robot_id', 'group_id'])
            ->toArray();
    }

}